<?php
//Include Config File and Check User 
include("./include/config.inc.php");
if(!isset($_SESSION['s_activId']))
{
  header('location:checkLogin.php');
}

//Main Varaible
$today = date('Y-m-d');
$regularArray = array();
$i=0;

//Select Type of Loan then Submit 
if(isset($_POST['SubmitForSold']))
{
  $selectRegular = "SELECT loan.loanId, loan.addOnLoanId, party.mobile, party.partyName, item.itemName, loan.weight, loan.value, itemlocation.itemlocationName, loan.pendingAmount, loan.soldNarration, loan.soldDate
                      FROM loan
                 LEFT JOIN item ON loan.itemId = item.itemId
                 LEFT JOIN itemlocation ON loan.itemlocationId = itemlocation.itemlocationId
                 LEFT JOIN party ON party.partyId = loan.PartyId";
                      
  //Regular Loan Type Process
  if($_POST['selectLoanType'] == "Regular")
  {
    $selectRegular    = $selectRegular ." WHERE loan.isSold = 'N'";
    $selectRegularRes = mysql_query($selectRegular);
    while($regularRow = mysql_fetch_array($selectRegularRes))
    {
      //Pendding Month Process for Regular Loan
  	  $lastInterestMonth     = "SELECT COUNT(isReceive) AS pendingMonth
  	                              FROM interestreceived
  	                             WHERE loanId = ".$regularRow['loanId']."
  	                               AND isReceive = 0
  	                               AND dateOfInstallment < '".$today."'";
  	  $lastInterestMonthResult = mysql_query($lastInterestMonth);
  	  $lastInterestMonthRow    = mysql_fetch_array($lastInterestMonthResult);
  	  
  	  //Pendding Interest Process for Regular Loan 
    	$selectPandingInterest    = "SELECT COUNT(*) AS totalPendingInterest
                                     FROM interestreceived
                                    WHERE loanId = ".$regularRow['loanId']."
                                      AND isReceive = 0
                                      AND dateOfInstallment < '".$today."' ";
      $selectPandingInterestRes = mysql_query($selectPandingInterest);
      $selectPandingInterestRow = mysql_fetch_array($selectPandingInterestRes);
      $selectLoanAmount = "SELECT SUM(pendingAmount) AS totaLoanAmount
                             FROM loan
                            WHERE 1 = 1 ";
      if($regularRow['addOnLoanId'] != "")
      {
        $selectLoanAmount .= " AND loanId = ".$regularRow['addOnLoanId']."
                               OR addOnLoanId = ".$regularRow['addOnLoanId'];
      }
      else
      {
        $selectLoanAmount .= " AND loanId = ".$regularRow['loanId']."
                               OR addOnLoanId = ".$regularRow['loanId']." ";
      }
      $selectLoanAmountRes = mysql_query($selectLoanAmount);
      if($selectLoanAmountRow = mysql_fetch_array($selectLoanAmountRes))
      {
      	$regularArray[$i]['totalLoanOnThisItem'] = $selectLoanAmountRow['totaLoanAmount'];
      }
      else
      {
      	$regularArray[$i]['totalLoanOnThisItem'] = 0;
      }
      
      if($selectPandingInterestRow['totalPendingInterest'] < 9 )
      {
    	  $regularArray[$i]['loanId']           = $regularRow['loanId'];
    	  $regularArray[$i]['addOnLoanId']      = $regularRow['addOnLoanId'];
    	  $regularArray[$i]['partyName']        = $regularRow['partyName'];
    	  $regularArray[$i]['itemName']         = $regularRow['itemName'];
    	  $regularArray[$i]['weight']           = $regularRow['weight'];
    	  $regularArray[$i]['value']            = $regularRow['value'];
    	  $regularArray[$i]['itemlocationName'] = $regularRow['itemlocationName'];
    	  $regularArray[$i]['mobile']           = $regularRow['mobile'];
    	  $regularArray[$i]['pendingAmount']    = $regularRow['pendingAmount'];
    	  $regularArray[$i]['pendingMonth']     = $lastInterestMonthRow['pendingMonth'];
    	  $i++;
      }
    }
  }
  
  //Not Regular Loan Type Process
  if($_POST['selectLoanType'] == "Not_Regular")
  {
  	$selectRegular    = $selectRegular ." WHERE loan.isSold = 'N'";
    $selectRegularRes = mysql_query($selectRegular);
    while($regularRow = mysql_fetch_array($selectRegularRes))
    {
      //Pendding Month Process for Not Regular Loan
  	  $lastInterestMonth       = "SELECT COUNT(isReceive) AS pendingMonth
  	                              FROM interestreceived
  	                             WHERE loanId = ".$regularRow['loanId']."
  	                               AND isReceive = 0
  	                               AND dateOfInstallment < '".$today."'";
  	  $lastInterestMonthResult = mysql_query($lastInterestMonth);
  	  $lastInterestMonthRow    = mysql_fetch_array($lastInterestMonthResult);
  	  
    	//Pendding Interest Process for Not Regular Loan
    	$selectPandingInterest    = "SELECT COUNT(*) AS totalPendingInterest
                                     FROM interestreceived
                                    WHERE loanId = ".$regularRow['loanId']."
                                      AND isReceive = 0
                                      AND dateOfInstallment < '".$today."' ";
      $selectPandingInterestRes = mysql_query($selectPandingInterest);
      $selectPandingInterestRow = mysql_fetch_array($selectPandingInterestRes);
      

      if($selectPandingInterestRow['totalPendingInterest'] >= 9 )
      {
      	$selectLoanAmount = "SELECT SUM(pendingAmount) AS totaLoanAmount
                               FROM loan
                              WHERE 1 = 1 ";
        if($regularRow['addOnLoanId'] != "")
        {
          $selectLoanAmount .= " AND loanId = ".$regularRow['addOnLoanId']."
                                 OR addOnLoanId = ".$regularRow['addOnLoanId'];
        }
        else
        {
          $selectLoanAmount .= " AND loanId = ".$regularRow['loanId']."
                                 OR addOnLoanId = ".$regularRow['loanId']." ";
        }
        $selectLoanAmountRes = mysql_query($selectLoanAmount);
        if($selectLoanAmountRow = mysql_fetch_array($selectLoanAmountRes))
        {
        	$regularArray[$i]['totalLoanOnThisItem'] = $selectLoanAmountRow['totaLoanAmount'];
        }
        else
        {
        	$regularArray[$i]['totalLoanOnThisItem'] = 0;
        }
    	  $regularArray[$i]['loanId']           = $regularRow['loanId'];
    	  $regularArray[$i]['addOnLoanId']      = $regularRow['addOnLoanId'];
    	  $regularArray[$i]['partyName']        = $regularRow['partyName'];
    	  $regularArray[$i]['itemName']         = $regularRow['itemName'];
    	  $regularArray[$i]['weight']           = $regularRow['weight'];
    	  $regularArray[$i]['value']            = $regularRow['value'];
    	  $regularArray[$i]['itemlocationName'] = $regularRow['itemlocationName'];
    	  $regularArray[$i]['mobile']           = $regularRow['mobile'];
    	  $regularArray[$i]['pendingAmount']    = $regularRow['pendingAmount'];
    	  $regularArray[$i]['pendingMonth']     = $lastInterestMonthRow['pendingMonth'];
    	  $i++;
      }
    }
  }
  
  //Sold Item Process
  if($_POST['selectLoanType'] == "Sold")
  {
    $selectRegular    = $selectRegular." WHERE loan.isSold = 'Y'
                                           AND loan.isDeleted = 'N'";
    $selectRegularRes = mysql_query($selectRegular);
    while($regularRow = mysql_fetch_array($selectRegularRes))
    {
      //Pendding Month Process for Sold Item
  	  $lastInterestMonth       = "SELECT COUNT(isReceive) AS pendingMonth
  	                              FROM interestreceived
  	                             WHERE loanId = ".$regularRow['loanId']."
  	                               AND isReceive = 0
  	                               AND dateOfInstallment < '".$today."'";
  	  $lastInterestMonthResult = mysql_query($lastInterestMonth);
  	  $lastInterestMonthRow    = mysql_fetch_array($lastInterestMonthResult);
  	  $selectLoanAmount = "SELECT SUM(pendingAmount) AS totaLoanAmount
                             FROM loan
                            WHERE 1 = 1 ";
      if($regularRow['addOnLoanId'] != "")
      {
        $selectLoanAmount .= " AND loanId = ".$regularRow['addOnLoanId']."
                               OR addOnLoanId = ".$regularRow['addOnLoanId'];
      }
      else
      {
        $selectLoanAmount .= " AND loanId = ".$regularRow['loanId']."
                               OR addOnLoanId = ".$regularRow['loanId']." ";
      }
      $selectLoanAmountRes = mysql_query($selectLoanAmount);
      if($selectLoanAmountRow = mysql_fetch_array($selectLoanAmountRes))
      {
      	$regularArray[$i]['totalLoanOnThisItem'] = $selectLoanAmountRow['totaLoanAmount'];
      }
      else
      {
      	$regularArray[$i]['totalLoanOnThisItem'] = 0;
      }

    	$regularArray[$i]['loanId']           = $regularRow['loanId'];
    	$regularArray[$i]['addOnLoanId']      = $regularRow['addOnLoanId'];
    	$regularArray[$i]['partyName']        = $regularRow['partyName'];
    	$regularArray[$i]['itemName']         = $regularRow['itemName'];
    	$regularArray[$i]['weight']           = $regularRow['weight'];
    	$regularArray[$i]['value']            = $regularRow['value'];
    	$regularArray[$i]['itemlocationName'] = $regularRow['itemlocationName'];
    	$regularArray[$i]['mobile']           = $regularRow['mobile'];
    	$regularArray[$i]['pendingAmount']    = $regularRow['pendingAmount'];
    	$regularArray[$i]['soldNarration']    = $regularRow['soldNarration'];
    	$regularArray[$i]['soldDate']         = date('d-m-Y',strtotime($regularRow['soldDate']));
    	$regularArray[$i]['pendingMonth']     = $lastInterestMonthRow['pendingMonth'];
    	$i++;
    }
  }

}
else
{
  //Page Load Process
  $selectRegular    = "SELECT loan.loanId, loan.addOnLoanId, party.mobile, party.partyName, item.itemName, loan.weight, loan.value, itemlocation.itemlocationName, loan.pendingAmount, loan.soldNarration
                         FROM loan
                    LEFT JOIN item ON loan.itemId = item.itemId
                    LEFT JOIN itemlocation ON loan.itemlocationId = itemlocation.itemlocationId
                    LEFT JOIN party ON party.partyId = loan.PartyId
                        WHERE loan.isSold = 'N'";
  $selectRegularRes = mysql_query($selectRegular);
  while($regularRow = mysql_fetch_array($selectRegularRes))
  {
  	$selectLoanAmount = "SELECT SUM(pendingAmount) AS totaLoanAmount
                             FROM loan
                            WHERE 1 = 1 ";
      if($regularRow['addOnLoanId'] != "")
      {
        $selectLoanAmount .= " AND loanId = ".$regularRow['addOnLoanId']."
                               OR addOnLoanId = ".$regularRow['addOnLoanId'];
      }
      else
      {
        $selectLoanAmount .= " AND loanId = ".$regularRow['loanId']."
                               OR addOnLoanId = ".$regularRow['loanId']." ";
      }
      $selectLoanAmountRes = mysql_query($selectLoanAmount);
      if($selectLoanAmountRow = mysql_fetch_array($selectLoanAmountRes))
      {
      	$regularArray[$i]['totalLoanOnThisItem'] = $selectLoanAmountRow['totaLoanAmount'];
      }
      else
      {
      	$regularArray[$i]['totalLoanOnThisItem'] = 0;
      }
    //Pendding Month Process
  	$lastInterestMonth       = "SELECT COUNT(isReceive) AS pendingMonth
  	                              FROM interestreceived
  	                             WHERE loanId = ".$regularRow['loanId']."
  	                               AND isReceive = 0
  	                               AND dateOfInstallment < '".$today."'";
  	$lastInterestMonthResult = mysql_query($lastInterestMonth);
  	$lastInterestMonthRow    = mysql_fetch_array($lastInterestMonthResult);
  	
  	//Pendding Interest Process
  	$selectPandingInterest    = "SELECT COUNT(*) AS totalPendingInterest
                                   FROM interestreceived
                                  WHERE loanId = ".$regularRow['loanId']."
                                    AND isReceive = 0
                                    AND dateOfInstallment < '".$today."' ";
    $selectPandingInterestRes = mysql_query($selectPandingInterest);
    $selectPandingInterestRow = mysql_fetch_array($selectPandingInterestRes);
    if($selectPandingInterestRow['totalPendingInterest'] < 9 )
    {
  	  $regularArray[$i]['loanId']           = $regularRow['loanId'];
  	  $regularArray[$i]['addOnLoanId']      = $regularRow['addOnLoanId'];
  	  $regularArray[$i]['partyName']        = $regularRow['partyName'];
  	  $regularArray[$i]['itemName']         = $regularRow['itemName'];
  	  $regularArray[$i]['weight']           = $regularRow['weight'];
  	  $regularArray[$i]['value']            = $regularRow['value'];
  	  $regularArray[$i]['itemlocationName'] = $regularRow['itemlocationName'];
  	  $regularArray[$i]['mobile']           = $regularRow['mobile'];
  	  $regularArray[$i]['pendingAmount']    = $regularRow['pendingAmount'];
  	  $regularArray[$i]['pendingMonth']     = $lastInterestMonthRow['pendingMonth'];
  	  $i++;
  	}
  }
}

//Select Type of Loan 
$comboSelected   = isset($_REQUEST['selectLoanType']) ? $_REQUEST['selectLoanType'] : "Regular";
$i = 0;
$comboOutput[$i] = "Regular";
$comboValues[$i] = "Regular";
$i++;
$comboOutput[$i] = "Not_Regular";
$comboValues[$i] = "Not_Regular";
$i++;
$comboOutput[$i] = "Sold";
$comboValues[$i] = "Sold";
$i++;
///////////////

//Bottom of the File and Assign Smarty Variable
include("./bottom.php");
$smarty->assign('regularArray',$regularArray); 	
$smarty->assign('comboOutput',$comboOutput); 
$smarty->assign('comboValues',$comboValues); 
$smarty->assign('comboSelected',$comboSelected); 
$smarty->display('soldItem.tpl');
?>